package model;

import java.sql.Connection;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;

import beans.EventBean;
import beans.MessageBean;
import beans.UserBean;
import beans.VenueBean;

public class EventDAO {
	static Connection currentCon = null;
	static ResultSet rs = null;

	public static EventBean createEvent(EventBean newEvent) {
		Statement stmt = null;    

		String insertQuery = "insert into event(name,starttimestamp,host,endtime,description,numberparticipants,numberguestspots,type,venue) values ('" +
				newEvent.getName() + "', '" +
				newEvent.getStartTimeStamp() + "', '" +
				newEvent.getHost() + "', '" +
				newEvent.getEndTimeStamp() + "', '" +
				newEvent.getDescription() + "', '" +
				newEvent.getParticipants() + "', '" +
				newEvent.getGuests() + "', '" +
				newEvent.getType() + "', '" +
				newEvent.getVenue().getId() + "')";

		try 
		{
			currentCon = ConnectionManager.getConnection();
			stmt = currentCon.createStatement();
			stmt.executeUpdate(insertQuery);	        
		} 

		catch (Exception ex) 
		{
			System.out.println("Log In failed: An Exception has occurred! " + ex);
		} 

		finally 
		{
			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception e) {}
				stmt = null;
			}

			if (currentCon != null) {
				try {
					currentCon.close();
				} catch (Exception e) {
				}

				currentCon = null;
			}
		}
		return newEvent;
	}

	public static EventBean getEvent(EventBean event) {
		Statement stmt = null;    

		String host = event.getHost();
		Timestamp start = event.getStartTimeStamp();

		String searchQuery =
				"select * from event where host='"
						+ host + "' and starttimestamp='"
						+ start.toString() +"'";
		try {
			currentCon = ConnectionManager.getConnection();
			stmt = currentCon.createStatement();
			rs = stmt.executeQuery(searchQuery);
			int venueId = 0;

			while (rs.next()) {
				event.setName(rs.getString("name"));
				event.setStartTimeStamp(rs.getTimestamp("starttimestamp"));
				event.setHost(rs.getString("host"));
				event.setFlyerURL(rs.getString("flyerurl"));
				event.setEndTimeStamp(rs.getTimestamp("endtime"));
				event.setDescription(rs.getString("description"));
				event.setParticipants(rs.getInt("numberparticipants"));
				event.setGuests(rs.getInt("numberguestspots"));
				java.util.Date now = new java.util.Date();
				if (now.after(rs.getTimestamp("endtime")))
					event.setFinished(true);
				else
					event.setFinished(false);
				event.setType(rs.getString("type"));
				venueId= rs.getInt("venue");
			}
			VenueBean venue = new VenueBean();
			venue.setId(venueId);
			event.setVenue(VenueDAO.getVenue(venue));

			String guestQuery = "select guest from attend where host='" + event.getHost() + "' and starttimestamp='" + event.getStartTimeStamp().toString() + "'";
			rs = stmt.executeQuery(guestQuery);
			while (rs.next()) {
				UserBean guest = new UserBean();
				guest.setEmail(rs.getString("email"));
				event.addGuest(UserDAO.visit(guest));
			}	
		} 

		catch (Exception ex) {
			System.out.println("Transfer failed: An Exception has occurred! " + ex);
		} 

		finally {
			if (rs != null)	{
				try {
					rs.close();
				} catch (Exception e) {}
				rs = null;
			}

			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception e) {}
				stmt = null;
			}

			if (currentCon != null) {
				try {
					currentCon.close();
				} catch (Exception e) {
				}

				currentCon = null;
			}
		}
		return event;
	}

	public static void attendEvent(UserBean guest, EventBean event) {
		Statement stmt = null;    

		String insertQuery = "insert into attend values ('" +
				guest.getEmail() + "', '" +
				event.getStartTimeStamp() + "', '" +
				event.getHost() + "')";

		String updatePoints = "update users set points=(points-1) where email='" + guest.getEmail() + "'";

		try 
		{
			currentCon = ConnectionManager.getConnection();
			stmt = currentCon.createStatement();
			stmt.executeUpdate(insertQuery);	        
			stmt.executeUpdate(updatePoints);
		} 

		catch (Exception ex) 
		{
			System.out.println("Log In failed: An Exception has occurred! " + ex);
		} 

		finally 
		{
			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception e) {}
				stmt = null;
			}

			if (currentCon != null) {
				try {
					currentCon.close();
				} catch (Exception e) {
				}

				currentCon = null;
			}
		}
	}

	public static void leaveEvent(UserBean guest, EventBean event) {
		Statement stmt = null;    

		String deleteQuery = "delete from attend where guest='" + guest.getEmail() + "' and eventtimestamp='" + event.getStartTimeStamp() + "' and host='"+ event.getHost() + "'";
		String updatePoints = "update users set points=(points+1) where email='" + guest.getEmail() + "'";

		try 
		{
			currentCon = ConnectionManager.getConnection();
			stmt = currentCon.createStatement();
			stmt.executeUpdate(deleteQuery);	        
			stmt.executeUpdate(updatePoints);	        
		} 

		catch (Exception ex) 
		{
			System.out.println("Remove failed: An Exception has occurred! " + ex);
		} 

		finally 
		{
			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception e) {}
				stmt = null;
			}

			if (currentCon != null) {
				try {
					currentCon.close();
				} catch (Exception e) {
				}

				currentCon = null;
			}
		}
	}

	public static ArrayList<UserBean> getGuests(EventBean event) {
		Statement stmt = null;    

		String searchQuery = "select guest from attend where eventtimestamp='" + event.getStartTimeStamp() + "' and host='" + event.getHost() + "'";

		ArrayList<UserBean> results = new ArrayList<UserBean>();
		try 
		{
			currentCon = ConnectionManager.getConnection();
			stmt = currentCon.createStatement();
			rs = stmt.executeQuery(searchQuery);	 
			while (rs.next()) {
				String email = rs.getString("guest");
				UserBean user = new UserBean();
				user.setEmail(email);
				user = UserDAO.visit(user);
				results.add(user);
			}
		} 

		catch (Exception ex) 
		{
			System.out.println("Search guestlist failed: An Exception has occurred! " + ex);
		} 

		finally 
		{
			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception e) {}
				stmt = null;
			}

			if (currentCon != null) {
				try {
					currentCon.close();
				} catch (Exception e) {
				}

				currentCon = null;
			}
		}
		return results;
	}

	public static void updateFlyerURL(EventBean event, String newURL) {
		Statement stmt = null;    

		String updateQuery = "update event set flyerurl='" + newURL + "' where host='" + event.getHost() + "' and starttimestamp='" + event.getStartTimeStamp() + "'";

		try 
		{
			currentCon = ConnectionManager.getConnection();
			stmt = currentCon.createStatement();
			stmt.executeUpdate(updateQuery);	        
		} 

		catch (Exception ex) 
		{
			System.out.println("Log In failed: An Exception has occurred! " + ex);
		} 

		finally 
		{
			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception e) {}
				stmt = null;
			}

			if (currentCon != null) {
				try {
					currentCon.close();
				} catch (Exception e) {
				}

				currentCon = null;
			}
		}
	}

	public static ArrayList<EventBean> getUsersEvents(UserBean user) {
		Statement stmt = null;    
		Statement stmt2 = null;

		String username = user.getEmail();

		String searchQuery = "select name,starttimestamp,event.host,endtime,guest FROM (event left join (select * from attend where guest='" + username + "') as table2 on (event.host,event.starttimestamp)=(table2.host,table2.eventtimestamp)) where event.host='" + username + "' or guest='" + username + "' order by starttimestamp desc";

		java.util.Date now = new java.util.Date();

		ArrayList<EventBean> eventList = new ArrayList<EventBean>();

		try {
			currentCon = ConnectionManager.getConnection();
			stmt = currentCon.createStatement();
			rs = stmt.executeQuery(searchQuery);

			while (rs.next()) {
				EventBean event = new EventBean();
				event.setName(rs.getString("name"));
				event.setStartTimeStamp(rs.getTimestamp("starttimestamp"));
				event.setHost(rs.getString("host"));
				event.setEndTimeStamp(rs.getTimestamp("endtime"));
				UserBean onerandomguest = new UserBean();
				onerandomguest.setEmail(rs.getString("guest"));
				ArrayList<UserBean> guestList = new ArrayList<UserBean>();
				guestList.add(onerandomguest);
				event.setGuestList(guestList);
				if (now.after(rs.getTimestamp("endtime")))
					event.setFinished(true);
				else
					event.setFinished(false);
				eventList.add(event);
			}
		} 

		catch (Exception ex) {
			System.out.println("FAIL!: An Exception has occurred! " + ex);
		} 

		finally {
			if (rs != null)	{
				try {
					rs.close();
				} catch (Exception e) {}
				rs = null;
			}

			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception e) {}
				stmt = null;
			}

			if (currentCon != null) {
				try {
					currentCon.close();
				} catch (Exception e) {
				}

				currentCon = null;
			}
		}
		return eventList;
	}

	public static EventBean getEventsRatings(UserBean user, EventBean event) {
		Statement stmt = null;
		if (user.getEmail().equals(event.getHost())) {
			String getRatingQuery = "select guest,guestrating from attend where host='" + event.getHost() + "' and eventtimestamp='" + event.getStartTimeStamp() + "'";
			try {
				currentCon = ConnectionManager.getConnection();
				stmt = currentCon.createStatement();
				rs = stmt.executeQuery(getRatingQuery);

				while (rs.next()) {
					if (!event.getGuestList().isEmpty())
						for (int i = 0; i < event.getGuestList().size(); i++) {
							if (event.getGuestList().get(i).getEmail().equals(rs.getString("guest"))) {
								event.getGuestList().get(i).setHostToGuestRating(rs.getInt("guestrating"));
							}
						}
				}
			}
			catch (Exception ex) {
				System.out.println("FAIL!: An Exception has occurred! " + ex);
			}	

			finally {
				if (rs != null)	{
					try {
						rs.close();
					} catch (Exception e) {}
					rs = null;
				}

				if (stmt != null) {
					try {
						stmt.close();
					} catch (Exception e) {}
					stmt = null;
				}

				if (currentCon != null) {
					try {
						currentCon.close();
					} catch (Exception e) {
					}

					currentCon = null;
				}
			}
		} 
		else {
			String getRatingQuery = "select guest,hostrating from attend where host='" + event.getHost() + "' and eventtimestamp='" + event.getStartTimeStamp() + "'";

			try {
				currentCon = ConnectionManager.getConnection();
				stmt = currentCon.createStatement();
				rs = stmt.executeQuery(getRatingQuery);

				while (rs.next()) {
					if (!event.getGuestList().isEmpty())
						for (int i = 0; i < event.getGuestList().size(); i++) {
							if (event.getGuestList().get(i).getEmail().equals(rs.getString("guest"))) {
								event.getGuestList().get(i).setGuestToHostRating(rs.getInt("hostrating"));
							}
						}
				}
			} 
			catch (Exception ex) {
				System.out.println("FAIL!: An Exception has occurred! " + ex);
			}	

			finally {
				if (rs != null)	{
					try {
						rs.close();
					} catch (Exception e) {}
					rs = null;
				}

				if (stmt != null) {
					try {
						stmt.close();
					} catch (Exception e) {}
					stmt = null;
				}

				if (currentCon != null) {
					try {
						currentCon.close();
					} catch (Exception e) {
					}

					currentCon = null;
				}
			}
		}
		return event;
	}

	public static EventBean getEventsRatingsForStats(UserBean user, EventBean event) {
		Statement stmt = null;
		if (!user.getEmail().equals(event.getHost())) {
			String getRatingQuery = "select guest,guestrating from attend where host='" + event.getHost() + "' and eventtimestamp='" + event.getStartTimeStamp() + "'";

			try {
				currentCon = ConnectionManager.getConnection();
				stmt = currentCon.createStatement();
				rs = stmt.executeQuery(getRatingQuery);

				while (rs.next()) {
					if (!event.getGuestList().isEmpty())
						for (int i = 0; i < event.getGuestList().size(); i++) {
							if (event.getGuestList().get(i).getEmail().equals(rs.getString("guest"))) {
								event.getGuestList().get(i).setHostToGuestRating(rs.getInt("guestrating"));
							}
						}
				}
			}

			catch (Exception ex) {
				System.out.println("FAIL!: An Exception has occurred! " + ex);
			}	

			finally {
				if (rs != null)	{
					try {
						rs.close();
					} catch (Exception e) {}
					rs = null;
				}

				if (stmt != null) {
					try {
						stmt.close();
					} catch (Exception e) {}
					stmt = null;
				}

				if (currentCon != null) {
					try {
						currentCon.close();
					} catch (Exception e) {
					}

					currentCon = null;
				}
			}
		} 
		else {
			String getRatingQuery = "select guest,hostrating from attend where host='" + event.getHost() + "' and eventtimestamp='" + event.getStartTimeStamp() + "'";

			try {
				currentCon = ConnectionManager.getConnection();
				stmt = currentCon.createStatement();
				rs = stmt.executeQuery(getRatingQuery);

				while (rs.next()) {
					if (!event.getGuestList().isEmpty())
						for (int i = 0; i < event.getGuestList().size(); i++) {
							if (event.getGuestList().get(i).getEmail().equals(rs.getString("guest"))) {
								event.getGuestList().get(i).setGuestToHostRating(rs.getInt("hostrating"));
							}
						}
				}
			} 

			catch (Exception ex) {
				System.out.println("FAIL!: An Exception has occurred! " + ex);
			}

			finally {
				if (rs != null)	{
					try {
						rs.close();
					} catch (Exception e) {}
					rs = null;
				}

				if (stmt != null) {
					try {
						stmt.close();
					} catch (Exception e) {}
					stmt = null;
				}

				if (currentCon != null) {
					try {
						currentCon.close();
					} catch (Exception e) {
					}

					currentCon = null;
				}
			}
		}
		return event;
	}

	public static void rate(UserBean user, EventBean event) {
		Statement stmt = null;
		if (user.getGuestToHostRating() != 0) {
			String rate = "update attend set hostrating=" + user.getGuestToHostRating() + " where host='" + event.getHost() + "' and eventtimestamp='" + event.getStartTimeStamp() + "' and guest='" + user.getEmail() + "'";

			try {
				currentCon = ConnectionManager.getConnection();
				stmt = currentCon.createStatement();
				stmt.executeUpdate(rate);
			}

			catch (Exception ex) {
				System.out.println("FAIL!: An Exception has occurred! " + ex);
			}

			finally {
				if (rs != null)	{
					try {
						rs.close();
					} catch (Exception e) {}
					rs = null;
				}

				if (stmt != null) {
					try {
						stmt.close();
					} catch (Exception e) {}
					stmt = null;
				}

				if (currentCon != null) {
					try {
						currentCon.close();
					} catch (Exception e) {
					}

					currentCon = null;
				}
			}
		} 
		else {
			for (UserBean guest : event.getGuestList()) {
				if (guest.getHostToGuestRating() != 0) {
					String rate = "update attend set guestrating=" +guest.getHostToGuestRating() + " where host='" + event.getHost() + "' and eventtimestamp='" + event.getStartTimeStamp() + "' and guest='" + guest.getEmail() + "'";

					try {
						currentCon = ConnectionManager.getConnection();
						stmt = currentCon.createStatement();
						stmt.executeUpdate(rate);
					}

					catch (Exception ex) {
						System.out.println("FAIL!: An Exception has occurred! " + ex);
					}	

					finally {
						if (rs != null)	{
							try {
								rs.close();
							} catch (Exception e) {}
							rs = null;
						}

						if (stmt != null) {
							try {
								stmt.close();
							} catch (Exception e) {}
							stmt = null;
						}

						if (currentCon != null) {
							try {
								currentCon.close();
							} catch (Exception e) {
							}

							currentCon = null;
						}
					}
				} 
			}
		}
	}

	public static ArrayList<String> getTypes(String ambit) {
		Statement stmt = null;    
		String searchQuery = "select name from type where ambit='" + ambit +"' order by name asc";

		ArrayList<String> typesList = new ArrayList<String>();

		try {
			currentCon = ConnectionManager.getConnection();
			stmt = currentCon.createStatement();
			rs = stmt.executeQuery(searchQuery);	
			while(rs.next()) {
				typesList.add(rs.getString("name"));
			}
		} 

		catch (Exception ex) {
			System.out.println("FAIL!: An Exception has occurred! " + ex);
		} 

		finally {
			if (rs != null)	{
				try {
					rs.close();
				} catch (Exception e) {}
				rs = null;
			}

			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception e) {}
				stmt = null;
			}

			if (currentCon != null) {
				try {
					currentCon.close();
				} catch (Exception e) {}
				currentCon = null;
			}
		}
		return typesList;
	}

	public static void delete(EventBean event) {
		Statement stmt = null;    
		String deleteWriteAbout = "delete from writeabout where host='" + event.getHost() + "' and eventtimestamp='" + event.getStartTimeStamp() + "'";
		String deleteAttend = "delete from attend where host='" + event.getHost() + "' and eventtimestamp='" + event.getStartTimeStamp() + "'";
		String deleteEvent = "delete from event where host='" + event.getHost() + "' and starttimestamp='" + event.getStartTimeStamp() + "'";

		try {
			currentCon = ConnectionManager.getConnection();
			stmt = currentCon.createStatement();
			stmt.executeUpdate(deleteWriteAbout);	
			stmt.executeUpdate(deleteAttend);	
			stmt.executeUpdate(deleteEvent);	
		} 

		catch (Exception ex) {
			System.out.println("FAIL!: An Exception has occurred! " + ex);
		} 

		finally {
			if (rs != null)	{
				try {
					rs.close();
				} catch (Exception e) {}
				rs = null;
			}

			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception e) {}
				stmt = null;
			}

			if (currentCon != null) {
				try {
					currentCon.close();
				} catch (Exception e) {}
				currentCon = null;
			}
		}
	}

	public static EventBean editEvent(EventBean oldevent, EventBean newevent) {
		Statement stmt = null;    

		String updateQuery = "update event set name='" + newevent.getName() + "', starttimestamp='" + newevent.getStartTimeStamp() + "', endtime='" + newevent.getEndTimeStamp() + "', description='" + newevent.getDescription() + "', numberparticipants='" + newevent.getParticipants() + "', numberguestspots='" + newevent.getGuests() + "', type='" + newevent.getType() + "', venue='" + newevent.getVenue().getId() + "' where host='" + oldevent.getHost() + "' and starttimestamp='" + oldevent.getStartTimeStamp() + "'";

		try 
		{
			currentCon = ConnectionManager.getConnection();
			stmt = currentCon.createStatement();
			stmt.executeUpdate(updateQuery);	        
		} 

		catch (Exception ex) 
		{
			System.out.println("Log In failed: An Exception has occurred! " + ex);
		} 

		finally 
		{
			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception e) {}
				stmt = null;
			}

			if (currentCon != null) {
				try {
					currentCon.close();
				} catch (Exception e) {
				}

				currentCon = null;
			}
		}
		return newevent;
	}

	public static ArrayList<EventBean> search(EventBean event) {

		Statement stmt = null; 
		String name = event.getName();  
		VenueBean venue = event.getVenue();
		String country = venue.getCountry();
		String region = venue.getRegion();
		String city = venue.getCity();
		Timestamp fromtimestamp = event.getStartTimeStamp(); 
		Timestamp totimestamp = event.getEndTimeStamp();

		String searchQuery = "select * from (event join (select id,city,region,country from venue) as t1 on event.venue=t1.id) as table1 where name ilike '%" + name + "%' and country ilike '%" + country + "%' and region ilike '%" + region + "%' and city ilike '%" + city + "%'";

		if (fromtimestamp != null)
			searchQuery += " and starttimestamp >= '"+ fromtimestamp +"'";
		if (totimestamp != null)
			searchQuery += " and endtime <= '" + totimestamp + "'";
		ArrayList<EventBean> searchResults = new ArrayList<EventBean>();

		try {
			currentCon = ConnectionManager.getConnection();
			stmt = currentCon.createStatement();
			rs = stmt.executeQuery(searchQuery);	        

			while (rs.next()) {

				EventBean eventresult = new EventBean();
				eventresult.setHost(rs.getString("host"));
				eventresult.setName(rs.getString("name")); //set event name
				eventresult.setStartTimeStamp(rs.getTimestamp("starttimestamp")); //set startimestamp
				eventresult.setGuests(rs.getInt("numberguestspots")); //set #guest spots
				eventresult.setParticipants(rs.getInt("numberparticipants")); //#number of participants

				searchResults.add(eventresult);

			}
		} 

		catch (Exception ex) {
			System.out.println("Transfer failed: An Exception has occurred! " + ex);
		} 

		finally {
			if (rs != null)	{
				try {
					rs.close();
				} catch (Exception e) {}
				rs = null;
			}

			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception e) {}
				stmt = null;
			}

			if (currentCon != null) {
				try {
					currentCon.close();
				} catch (Exception e) {
				}

				currentCon = null;
			}
		}
		return searchResults;

	}
}